/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Database;

import Domain.ApartmentBooking;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author Min0
 */
public class DBApartmentBooking implements IFDBApartmentBooking
{
    private Connection con;

    public DBApartmentBooking()
    {
        con = DBConnection.getInstance().getDBcon();
    }

    //START private methods for class
    private ApartmentBooking BuildApartmentBooking(ResultSet results)
    {
        ApartmentBooking appObj = new ApartmentBooking();
        try
        {
            appObj.setBookingID(results.getString("BookingID"));
            appObj.setCustomerID(results.getString("CustomerID"));
            appObj.setTravelAgency(results.getString("TravelAgency"));
            appObj.setCheckInDate(results.getString("CheckInDate"));
            appObj.setNoOfNights(results.getInt("NoOfNights"));
            appObj.setApartmentNo(results.getInt("ApartmentNo"));
        } catch (Exception e)
        {
            System.out.println("error in building the ApartmentBooking object\n" + e.getMessage());
        }
        return appObj;
    }

    private String BuildQuery(String wClause)
    {
        String query = "SELECT * FROM ApartmentBooking";

        if (wClause.length() > 0)
        {
            query = query + " WHERE " + wClause;
        }

        return query;
    }

    private ApartmentBooking singleWhere(String wClause)
    {
        ResultSet results;
        ApartmentBooking appObj = new ApartmentBooking();
        String query = BuildQuery(wClause);
        System.out.println(query);
        try
        {
            java.sql.Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if (results.next())
            {
                appObj = BuildApartmentBooking(results);
                //assocaition is to be build
                stmt.close();
            }

        }//end try	
        catch (Exception e)
        {
            System.out.println("Query exception: " + e);
        }
        return appObj;
    }

    private ArrayList<ApartmentBooking> miscWhere(String wClause)
    {
        ResultSet results;
        ArrayList<ApartmentBooking> list = new ArrayList<ApartmentBooking>();

        String query = BuildQuery(wClause);

        try
        { // read the ApartmentBooking from the database
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            while (results.next())
            {
                ApartmentBooking appObj = new ApartmentBooking();
                appObj = BuildApartmentBooking(results);
                list.add(appObj);
            }//end while
            stmt.close();
        } catch (Exception e)
        {
            System.out.println("Query exception - select: " + e);
        }
        return list;
    }
    //END private methods for class

    @Override
    public void insertApartmentBooking(ApartmentBooking apBooking)
    {
        String query = "INSERT INTO ApartmentBooking(BookingID,CustomerID,TravelAgency,CheckInDate,NoOfNights,ApartmentNo) VALUES('"
                + apBooking.getBookingID() + "','"
                + apBooking.getCustomerID() + "','"
                + apBooking.getTravelAgency() + "','"
                + apBooking.getCheckInDate() + "',"
                + apBooking.getNoOfNights() + ","
                + apBooking.getApartmentNo() + ")";
        
        System.out.println("insert: " + query);
        try
        {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            stmt.executeUpdate(query);
            stmt.close();
        }//end try	
        catch (Exception ex)
        {
            System.out.println("Insert exception in apartment booking db: " + ex);
        }
    }

    @Override
    public void insertApartmentBooking(String BookingID, String CustomerID, String TravelAgency, String CheckInDate, int NoOfNights, int ApartmentNo)
    {
        String query = "INSERT INTO ApartmentBooking(BookingID,CustomerID,TravelAgency,CheckInDate,NoOfNights,ApartmentNo) VALUES('"
                + BookingID + "','"
                + CustomerID + "','"
                + TravelAgency + "','"
                + CheckInDate + "',"
                + NoOfNights + ","
                + ApartmentNo + ")";
        
        System.out.println("insert: " + query);
        try
        {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            stmt.executeUpdate(query);
            stmt.close();
        }//end try	
        catch (Exception ex)
        {
            System.out.println("Insert exception in apartment booking db: " + ex);
        }
    }

    @Override
    public void insertApartmentBooking(String BookingID, String CustomerID, String CheckInDate, int NoOfNights, int ApartmentNo)
    {
        String query = "INSERT INTO ApartmentBooking(BookingID,CustomerID,CheckInDate,NoOfNights,ApartmentNo) VALUES('"
                + BookingID + "','"
                + CustomerID + "','"
                + CheckInDate + "',"
                + NoOfNights + ","
                + ApartmentNo + ")";
        
        System.out.println("insert: " + query);
        try
        {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            stmt.executeUpdate(query);
            stmt.close();
        }//end try	
        catch (Exception ex)
        {
            System.out.println("Insert exception in apartment booking db: " + ex);
        }
    }

    @Override
    public ApartmentBooking getApartmentBooking(String BookingID)
    {
        String wClause = " BookingID = '" + BookingID + "'";
        return singleWhere(wClause);
    }

    @Override
    public ArrayList<ApartmentBooking> getApartmentBookingList()
    {
        return miscWhere("");
    }

    @Override
    public void updateApartmentBooking(String BookingID, String CustomerID, String TravelAgency, String CheckInDate, int NoOfNights, int ApartmentNo)
    {
        String query = "UPDATE ApartmentBooking SET "
                + "CustomerID = '" + CustomerID + "',"
                + "CheckInDate = '" + CheckInDate + "',"
                + "NoOfNights = " + NoOfNights + ","
                + "ApartmentNo = " + ApartmentNo 
                + "WHERE BookingID = '" + BookingID + "'";
        
        System.out.println("Update query:" + query);
        try
        {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            stmt.executeUpdate(query);
            stmt.close();
        } catch (Exception ex)
        {
            System.out.println("Update exception in apartment booking db: " + ex);
        }
    }

    @Override
    public void deleteApartmentBooking(String BookingID)
    {
        String query = "DELETE FROM ApartmentBooking WHERE BookingID = '"
                + BookingID + "'";
        System.out.println(query);
        try
        { // delete
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            stmt.executeUpdate(query);
            stmt.close();
        } catch (Exception ex)
        {
            System.out.println("Delete exception in apartment booking db: " + ex);
        }
    }

    @Override
    public int getMax()
    {
        int max = getMax.getMaxId("Select max(BookingID) from ApartmentBooking");
        return max;
    }
}
